package com.dsmbase.corp.project.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import java.util.List;
import com.dsmbase.corp.project.domain.WorkLoad;

@Component
public class WorkLoadDao {

	private JdbcTemplate jdbcTemplate;
	
	private static final String SELECT_BY_ID_SQL = "select id, project_node_id, work_load, month from work_load where id = ?";
	private static final String SELECT_BY_KEY_SQL = "select id, project_node_id, work_load, month from work_load where project_node_id = ? and month = ?";
	private static final String UPDATE_SQL = "update work_load set work_load = ? where id = ?";
	private static final String SELECT_BY_PROJECT_NODE_ID = "select * from work_load where project_node_id = ?";
	private static final String INSERT_SQL = "insert work_load values(null, ?, ?, ?)";
	
	@Autowired
	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource ds) {
		dataSource = ds;
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public WorkLoad getWorkLoadByKey(int nodeId, int key) throws SQLException {
		Object[] params = new Object[] { nodeId, key };
		WorkLoad workLoad = null;
		try {
			workLoad = (WorkLoad) jdbcTemplate.queryForObject(SELECT_BY_KEY_SQL,
					params, new WorkLoadRowMapper());
		} catch (EmptyResultDataAccessException e) {
			throw new SQLException(e);
		}
		return workLoad;
	}
	
	public WorkLoad getWorkLoadById(int id) throws SQLException {
		Object[] params = new Object[] { id };
		WorkLoad workLoad = null;
		try {
			workLoad = (WorkLoad) jdbcTemplate.queryForObject(SELECT_BY_ID_SQL,
					params, new WorkLoadRowMapper());
		} catch (EmptyResultDataAccessException e) {
			throw new SQLException(e);
		}
		return workLoad;
	}
	
	public List<WorkLoad> getWorkLoadByProjectNodeId(int nodeId) throws SQLException {
		Object[] params = new Object[] { nodeId };
		List<WorkLoad> list = null;
		list = jdbcTemplate.query(SELECT_BY_PROJECT_NODE_ID, params , new WorkLoadRowMapper());
		return list;
	}
	
	public void insertWorkLoad(WorkLoad workLoad) throws SQLException {
		Object[] params = new Object[] { workLoad.getProjectNodeId(), workLoad.getWorkLoad(), workLoad.getMonth() };
		int[] argTypes = new int[] { Types.INTEGER, Types.INTEGER , Types.INTEGER };
		jdbcTemplate.update(INSERT_SQL, params, argTypes);
	}
	
	public void updateWorkLoad(WorkLoad workLoad) throws SQLException {
		Object[] params = new Object[] { workLoad.getWorkLoad(), workLoad.getId() }; 
		int[] types = new int[] { Types.INTEGER, Types.INTEGER }; 
		jdbcTemplate.update(UPDATE_SQL, params, types); 
	}
	
	public class WorkLoadRowMapper implements RowMapper<WorkLoad> {

		public WorkLoad mapRow(ResultSet rs, int index) throws SQLException {
			WorkLoad workLoad = new WorkLoad();
			workLoad.setId(rs.getInt("id"));
			workLoad.setProjectNodeId(rs.getInt("project_node_id"));
			workLoad.setMonth(rs.getInt("month"));
			workLoad.setWorkLoad(rs.getInt("work_load"));
			return workLoad;
		}

	}
	
}
